DROP TABLE COMMENTS;
DROP TABLE SOLUTION;
DROP TABLE PROBLEM;
DROP VIEW SECTION_GRAPH;
DROP TABLE SECTION;
DROP TABLE USERS;

CREATE TABLE USERS (
    userID					SERIAL PRIMARY KEY,
    name					varchar(10) NOT NULL CHECK (name <> ''),
    pass					varchar(10) NOT NULL CHECK (name <> '')
);

CREATE TABLE SECTION (
    sectionID           	SERIAL PRIMARY KEY,
    sectionName				varchar(80) NOT NULL CHECK (sectionName <> ''),
    parentSectionID			integer --null oznacza że jest to katalog główny
);

CREATE VIEW SECTION_GRAPH AS
	SELECT SECTION.sectionID AS sectionID, SECTION.parentSectionID AS parentSectionID
	FROM SECTION;

CREATE TABLE PROBLEM (
    problemID				SERIAL PRIMARY KEY,
    problemName				varchar(80) NOT NULL CHECK (problemName <> ''),
    problemDescription		text,
    sectionID				integer,
    problemStatus			varchar(40),
    main					text,
    owner				varchar(10) NOT NULL DEFAULT 'nobody' CHECK (owner <> '')
);

CREATE TABLE SOLUTION (
    solutionID				SERIAL PRIMARY KEY,
    problemID				integer NOT NULL,
    status					varchar(40),
    time					float
);

CREATE TABLE COMMENTS (
    commentID				SERIAL PRIMARY KEY,
    problemID				integer,
    message					text,
    userName				varchar(10),
    creationDate			varchar(40)
);

CREATE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION status_priority(name PROBLEM.problemStatus%TYPE)
returns int AS
$$
BEGIN
IF name = 'NEED FIX' THEN return 5; END IF;
IF name = 'NEED OPTIMIZING' THEN return 4; END IF;
IF name = 'NEED CODE QUALITY CHECK' THEN return 3; END IF;
IF name = 'TODO' THEN return 2; END IF;
IF name = 'DONE' THEN return 1; END IF;
return 0;
END;
$$
language plpgsql;

INSERT INTO USERS VALUES(0,'jaaro','abc');
INSERT INTO USERS VALUES(1,'dubel','abc');
INSERT INTO USERS VALUES(2,'dx','abc');

INSERT INTO SECTION VALUES(-1,'{KATALOG GŁÓWNY}',NULL);
